﻿using MySql.Data.MySqlClient;
using RantopadService.Conn;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace RantopadService.Business
{
    public class UserDriverVBusiness
    {
        public static DataTable getAllUserDriverVs(string wherelike)
        {
            string mysql = "SELECT  a.*, b.NAME as DEVICE_NAME from user_driver_v a,device b where (a.USER_ID like '%{0}%' )  and a.DEVICE_ID=b.ID ";
            //创建数据库连接
            mysql = string.Format(mysql, wherelike);
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();          
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);

            myconnection.Close();
            return dt;
        }
        public static DataTable getAllUserDriverVs(string wherelike,string deviceId)
        {
            string mysql = "SELECT  a.*, b.NAME as DEVICE_NAME from user_driver_v a,device b where (a.USER_ID like '%{0}%' ) and a.DEVICE_ID = '{1}' and a.DEVICE_ID=b.ID ";
            //string mysql = "SELECT * from user_driver_v  where (USER_ID like '%{0}%' ) and DEVICE_ID like '%{1}%'";
            if (deviceId == "0")
                return getAllUserDriverVs(wherelike);
            mysql = string.Format(mysql, wherelike, deviceId);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);

            myconnection.Close();
            return dt;
        }
        public static int getAllUserDriverVsCount()
        {
            string mysql = "SELECT count(*) as count from user_driver_v ";
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            {
                dr.Close();
                myconnection.Close();

                return 0;
            }
            dr.Close();
            var countstr = dr["count"].ToString();
            var count = Convert.ToInt32(countstr);
            dr.Close();
            myconnection.Close();
            return count;
        }
        public static int getAllUserDriverVsCount(string wherelike, string deviceId)
        {
            string mysql = "SELECT count(*) as count from user_driver_v where (USER_ID like '%{0}%' )  and DEVICE_ID like '%{1}%'";
            mysql = string.Format(mysql, wherelike,deviceId);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            {
                dr.Close();
                myconnection.Close();

                return 0;
            }
            var countstr = dr["count"].ToString();
            var count = Convert.ToInt32(countstr);
            dr.Close();
            myconnection.Close();
            return count;
        }
        public static int checkUserDriverVId(string user_driver_vID)
        {
            var count = checkData("USER_ID",user_driver_vID);
            return count;
        }
        public static int checkData(string colNM,string user_driver_vID)
        {
            string mysql = "SELECT count(*) as count from user_driver_v where {0} = '{1}' ";
            mysql = string.Format(mysql,colNM, user_driver_vID);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            {
                dr.Close();
                myconnection.Close();

                return 0;
            }
            var countstr = dr["count"].ToString();
            var count = Convert.ToInt32(countstr);
            dr.Close();
            myconnection.Close();
            return count;
        }

        public static int checkUserDriverVEmail(string user_driver_vEmail)
        {
            var count = checkData("EMAIL", user_driver_vEmail);
            return count;
        }
        public static int delUserDriverV(string userID,string deviceID)
        {
            string mysql = "delete from user_driver_v where USER_ID='{0}' and DEVICE_ID='{1}'";
            mysql = string.Format(mysql, userID, deviceID);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            
            var count=mycommand.ExecuteNonQuery();
            myconnection.Close();

            return count;
        }


        public static string getUserDriverV(string userID, string deviceID)
        {
            string mysql = "SELECT  a.*, b.NAME as DEVICE_NAME from user_driver_v a,device b where (a.USER_ID = '{0}' ) and a.DEVICE_ID = '{1}' and a.DEVICE_ID=b.ID ";
            mysql = string.Format(mysql, userID,  deviceID);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);
            myconnection.Close();

            return dt.Rows[0]["DRIVER_V"].ToString();
        }

        public static int addUserDriverV(string userID, string deviceID, string driverV)
        {
            //INSERT INTO `rantopad`.`user_driver_v` (`USER_ID`, `USER_PSW`, `EMAIL`, `DEVICE_ID`) VALUES ('aaaaa', 'a', 'liang@163.com', '0');
            int count = 0;//创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
            try
            {
                string mysql = "insert into `user_driver_v` (`USER_ID`, `DEVICE_ID`, `DRIVER_V`) VALUES ('{0}', '{1}', '{2}') ";
                mysql = string.Format(mysql,  userID,  deviceID,  driverV);
                
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;
            }
            

            return count;
        }

        internal static int updateUserDriverV(string userID, string deviceID, string driverV)
        {
            int count = 0;//创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
            try
            {
                string mysql = "update `user_driver_v` set `DRIVER_V`='{2}'  where `USER_ID`='{0}' and `DEVICE_ID`='{1}'";
                mysql = string.Format(mysql, userID, deviceID, driverV);
                
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception e)
            {
                myconnection.Close();
                NetLog.WriteTextLog(System.Reflection.MethodBase.GetCurrentMethod().Name.ToString(), e.Message.ToString(), DateTime.Now);
                count = 0;
            }


            return count;
        }
    }
}